XplentyでAPIを叩いてデータを取得してみる(政府統計の総合窓口(e-Stat))
はじめに
データアナリティクス事業本部のkobayashiです。
XplentyはETL、ELTツールとして様々なデータソースを扱え、また処理もXplentyのGUIで直感的に作成できます。 今回もXplentyを使ってAPIを叩いてデータを取しそのデータをDBに保存してみます。
Xplenty | Simplified ETL & ELT to BigQuery, Snowflake, Redshift & Azure
前回まではレスポンスの取り扱いにそれほど困ることがないパターンを扱いました。 今回は複雑なレスポンスを返すAPIのデータをXplentyで取り扱ってみたいと思い、e-StatのAPIをソースにし設定・ジョブ実行を行います。またトークンや統計表IDを変数に格納して汎用的に扱えるようにします。
前回の記事
APIで取得するデータ
今回は政府統計の総合窓口(e-Stat)のデータを取り扱います。 e-Statとは各省庁が実施している統計調査のデータを利用できるサイトです。
政府統計の総合窓口(e-Stat)は、各府省情報化統括責任者(CIO)連絡会議で決定された「統計調査等業務の業務・システム最適化計画」に基づき、日本の政府統計関係情報のワンストップサービスを実現するため2008年から本運用を開始した政府統計のポータルサイトです。
e-StatにはAPIがありユーザー登録をしてトークンを取得することでAPI経由でデータが取得できます。
今回はこの中の「平成27年国勢調査 小地域 年齢(5歳階級、4区分)別、男女別人口 東京都(統計表ID:8003000051)」のデータをXplentyを使って取得し、最終的にRedshiftへ保存します。
e-stat APIのレスポンスの確認
今回扱うAPIは政府統計の総合窓口(e-Stat)のAPI 仕様 3.0版 | 政府統計の総合窓口(e-Stat)−API機能になります。Curl等でリクエストURLを叩いてレスポンスを確認します。
リクエストURL
http://api.e-stat.go.jp/rest/3.0/app/json/getStatsData?appId={トークン}d&lang=J&statsDataId=8003000051&metaGetFlg=Y&explanationGetFlg=N&annotationGetFlg-N&cntGetFlg=N
レスポンス
{ "GET_STATS_DATA": { "RESULT": { "STATUS": 0, "ERROR_MSG": "正常に終了しました。", "DATE": "2020-06-24T10:34:26.720+09:00" }, "PARAMETER": { "LANG": "J", "STATS_DATA_ID": 8003000051, "NARROWING_COND": { "LEVEL_AREA_COND": 3 }, "DATA_FORMAT": "J", "START_POSITION": 1, "LIMIT": 10, "METAGET_FLG": "Y", "EXPLANATION_GET_FLG": "N", "CNT_GET_FLG": "N" }, "STATISTICAL_DATA": { ... }, "CLASS_INF": { "CLASS_OBJ": [ { "@id": "cat01", "@name": "年齢(5歳階級、4区分)別、男女別人口", "CLASS": [ { "@code": "0010", "@name": "総数、年齢「不詳」含む", "@level": "1", "@unit": "人" }, { "@code": "0020", "@name": " 総数0~4歳", "@level": "2", "@unit": "人", "@parentCode": "0010" }, ... ] }, { "@id": "cat02", "@name": "秘匿地域・合算地域有り", "CLASS": [ { "@code": "1", "@name": "無し", "@level": "1" }, { "@code": "2", "@name": "合算", "@level": "1" }, { "@code": "3", "@name": "秘匿", "@level": "1" } ] }, { "@id": "area", "@name": "東京都", "CLASS": [ { "@code": "13101001001", "@name": "千代田区丸の内1丁目", "@level": "3", "@parentCode": "131010010" }, { "@code": "13101001002", "@name": "千代田区丸の内2丁目", "@level": "3", "@parentCode": "131010010" }, ... ] } ] }, "DATA_INF": { "NOTE": { "@char": "-", "$": "当該数値がないもの" }, "VALUE": [ { "@cat01": "0010", "@cat02": "1", "@area": "13101005001", "@unit": "人", "$": "0" }, { "@cat01": "0010", "@cat02": "1", "@area": "13101008001", "@unit": "人", "$": "832" }, ... ] } } } }
レスポンスを見ていくと
$.GET_STATS_DATA.STATISTICAL_DATA
の中に欲しいデータが入っており、さらにその中のCLASS_INF.CLASS_OBJ
にcat01,cat02,area
のマスタ類がリストで入っています。更にその中のCLASS
にリストでマスタデータが入っています。
同じく$.GET_STATS_DATA.STATISTICAL_DATA
の中にDATA_INF
がありこの中のVALUE
にリスト形式で統計データが入っています。
Xplentyのパッケージの作成
Xplentyのパッケージの中身は大まかに以下になります。
- e-stat APIにリクエストを送りレスポンスを取得する
- 受け取ったデータをマスタ類と統計データに分ける
- マスタデータが入ったJsonデータを加工してRedshiftへ保存できる形に加工する
- 加工したマスタデータをマスタテーブル(
estat_mst
)に保存できるようにまとめる - 統計データが入ったJsonデータを加工してRedshiftへ保存できる形に加工する
- マスタテーブル(
estat_mst
)と統計データテーブル(estat_data
)用のRedshiftコンポーネントを作成する
これらのコンポーネントを組み合わせてパッケージを作成していきます。
はじめにサイドバーからPackages
を選択してNew Package
を押下し新しいパッケージを作成します。
1.e-stat API用にREST APIコンポーネントを作成
まずはe-statのAPIを使ってデータを取得し、加工・保存を行います。 またトークンが変わった際の利便性や統計表IDを簡単に切り替えて他のデータを取得しやすくするため、Xplentyのユーザー変数機能を使います。
手順1-2).Add another
を押下してトークン用、統計表ID用の変数を設定しSave
を押下して保存する。
key | value |
---|---|
appId | '{トークン}' |
statsDataId | '8003000051' |
文字列として扱うので値は必ず'
クォートで囲います。ここで変数を指定することで以降の手順中にて$appId,$statsDataId
の形で設定した値を取り出せます。
手順1-3).Add componet
を押下し、SourcesでREST API
を選択する
手順1-4).作成されたアイコンをダブルクリックするとREST APIコンポーネント設定用のモーダルが表示されるので、Authentication
でNone
を選択する。
手順1-5).Request and response
の設定を行いNext
を押下する。
- Method :
GET
を選択 - URL :
http://api.e-stat.go.jp/rest/3.0/app/json/getStatsData?appId=$appId&lang=J&statsDataId=$statsDataId&metaGetFlg=Y&explanationGetFlg=N&annotationGetFlg-N&cntGetFlg=N
を入力 -
Headers
- Key :
Content-Type
を入力 - VALUE :
application/json
を入力
- Key :
- Body : (空)
- Use Pagination : チェックしない
- Base record JSONPath Expression :
$.GET_STATS_DATA.STATISTICAL_DATA
を入力
他の設定値はデフォルトのままにします。
手順1-6).Response Schema
へ進み、Selected Fields
で右下のAdd
を押下し下記の設定値でSelected Fields
を設定する。その後Save
を押下し、設定を保存する。
Key | Alias | Type |
---|---|---|
CLASS_INF | CLASS_INF | MAP |
DATA_INF | DATA_INF | MAP |
この画面ではレスポンスの内容を確認できるので必要なデータを確認しながら設定を行えます。
これでREST APIコンポーネントの設定は終わりますので、次にデータ加工用のコンポーネントをを設定します。
2. 受け取ったデータをマスタ類と統計データに分ける
手順2-1).Add componet
を押下し、TransformationsでClone
を選択してCloneコンポーネントを作成しREST APIコンポーネントと接続する
3. マスタデータが入ったJsonデータを加工してRedshiftへ保存できる形に加工する
次にマスタデータを作成します。Selectコンポーネントを2つ繋げて、1つ目のコンポーネントでレスポンスをフラット化し、2つ目のコンポーネントでJson文字列を扱う関数を駆使して欲しい形に加工します。これをcat01,cat02,area
の3種類分設定します。
手順3-1).Add componet
を押下し、TransformationsでSelect
を選択する。この操作を2回行い2つSelectコンポーネントを作成する。
手順3-2).作成したSelectコンポーネントを前段のREST APIコンポーネントと接続する。(接続していないとカラムのマッピングができないため)
手順3-3).作成したSelectコンポーネントの1つ目を選択して設定を行いSave
を押下し保存する。
- EXPRESSION、ALIASに以下のように入力
EXPRESSION | ALIAS |
---|---|
JsonStringToMap(JsonExtract (CLASS_INF#'CLASS_OBJ','$.[0]'))#'@id' | class_id |
JsonStringToMap(JsonExtract (CLASS_INF#'CLASS_OBJ','$.[0]'))#'@name' | class_name |
Flatten(JsonStringToBag (JsonExtract (CLASS_INF#'CLASS_OBJ','$.[0].CLASS'))) | values |
ToString(SwitchTimeZone(CurrentTime(),'Asia/Tokyo'), 'yyyy-MM-dd\'T\'HH:mm:ss') | created_datetime |
手順3-4).作成したSelectコンポーネントの2つ目を選択して設定を行いSave
を押下し保存する。
- EXPRESSION、ALIASに以下のように入力
EXPRESSION | ALIAS |
---|---|
class_id | class_id |
class_name | class_name |
JsonStringToMap(values)#'@code' | code |
JsonStringToMap(values)#'@parentCode' | parentCode |
JsonStringToMap(values)#'@unit' | unit |
JsonStringToMap(values)#'@level' | level |
JsonStringToMap(values)#'@name' | name |
created_datetime | created_datetime |
手順3-1〜3-4を繰り返して3種類分作成します。
作成する際の手順時の変更点は1箇所です。
cat02
の作成をする際には手順3-3)でのEXPRESSION
の設定で'$.[0]'
の部分を'$.[1]'
に変更area
の作成をする際には手順3-3)でのEXPRESSION
の設定で'$.[0]'
の部分を'$.[2]'
に変更
設定が終わると下記の状態になります。
4. 加工したマスタデータをマスタテーブルに保存できるようにまとめる
cat01,cat02,area
で別テーブル分けるよりも1テーブルでまとめたほうが後々の扱いが楽なのでマスタテーブルとして1テーブルに保存します。また他の統計表IDのデータを入れることも考えstatsDataId
カラムも作成します。
手順4-1).Add componet
を押下し、TransformationsでUnion
を選択し前段のコンポーネント2つ(cat01,cat02
用)を接続する。
手順4-2).Unionコンポーネントをダブルクリックし設定画面に進み、Auto fill
を押下して自動設定でカラムマッピングを行う。
手順4-1、4-2を繰り返し、手順4-2)のUnionコンポーネントとarea
のSelectコンポーネントを結合します。
手順4-3).cat01,cat02,area
を結合したUnionコンポーネントに新規に作成したSelectコンポーネントを接続する。
手順4-4).Selectコンポーネントをダブルクリックし、カラムマッピングを行う。統計表IDを追加したいのでAuto fill
を押下して自動設定を行った後に以下を追加する。
- EXPRESSION :
'$statsDataId'
を入力(文字列として扱うので値は必ず'
クォートで囲む) - ALIAS :
statsDataId
を入力
ここまでの手順で下図の形になります。
5. 統計データが入ったJsonデータを加工してRedshiftへ保存できる形に加工する
マスタデータの作成は終わったので次に統計データを作成します。ここは再度Json文字列からデータベース用のデータを作成するので、Selectコンポーネントを2つ繋げて、1つ目のコンポーネントでレスポンスをフラット化し、2つ目のコンポーネントでJson文字列を扱う関数を駆使して欲しい形に加工します。
手順5-1).Add componet
を押下し、TransformationsでSelect
を選択する。この操作を2回行い2つSelectコンポーネントを作成する。
手順5-2).作成したSelectコンポーネントを前段のCloneコンポーネントと接続する。(接続していないとカラムのマッピングができないため)
手順3-3).作成したSelectコンポーネントの1つ目を選択して設定を行いSave
を押下し保存する。
- EXPRESSION、ALIASに以下のように入力
EXPRESSION | ALIAS |
---|---|
Flatten(JsonStringToBag (DATA_INF#'VALUE')) | values |
ToString(SwitchTimeZone(CurrentTime(),'Asia/Tokyo'), 'yyyy-MM-dd\'T\'HH:mm:ss') | created_datetime |
手順3-4).作成したSelectコンポーネントの2つ目を選択して設定を行いSave
を押下し保存する。
- EXPRESSION、ALIASに以下のように入力
EXPRESSION | ALIAS |
---|---|
'$statsDataId' | statsDataId |
JsonStringToMap(values)#'@cat01' | cat01 |
JsonStringToMap(values)#'@cat02' | cat02 |
JsonStringToMap(values)#'@area' | area |
JsonStringToMap(values)#'@unit' | unit |
JsonStringToMap(values)#'$' | cnt |
created_datetime | created_datetime |
6. 保存先としてRedshiftコンポーネント作成
前回までと同様に簡単に行うためテーブルは予め作成せずXplentyのジョブで自動作成し、データは毎回洗い替えを行う設定にします。
手順4-1).Add componet
を押下し、DestinationsでAmazon Redshift
を選択する
手順4-2).画面上にRedshiftのアイコンが作成されるので先に作成したSelectコンポーネントのアイコンとDrag&Dropで紐付ける。
手順4-3).RedshiftのアイコンをダブルクリックするとRedshiftコンポーネント設定用のモーダルが表示されるので、設定済みのRedshiftのconnectionを選択する。
手順4-4).移行先のRedshiftのスキーマとテーブルを入力し各種設定を行い、Next
を押下する。
- Automatically create table if it doesn't exist : チェックする
- Automatically add missing columns : チェックする
- Operation type :
Overwrite (Truncate and insert)
を選択する
他の設定はデフォルトで問題ありません。
これでパッケージの作成は完了です。最終的なパッケージの形は下図になります。
Xplentyのパッケージの実行と確認
実際にジョブを実行して結果を確認します。
Redshitでデータを確認
マスタテーブル
select * from cm_kobayashi_test.estat_mst;
statsdataid | class_id | class_name | code | parentcode | unit | level | name | created_datetime |
---|---|---|---|---|---|---|---|---|
8003000051 | cat01 | 年齢(5歳階級、4区分)別、男女別人口 | 0010 | NULL | 人 | 1 | 総数、年齢「不詳」含む | 2020-06-29T09:55:27 |
8003000051 | cat02 | 秘匿地域・合算地域有り | 2 | NULL | NULL | 1 | 合算 | 2020-06-29T09:55:27 |
8003000051 | area | 東京都 | 13101002001 | 131010020 | NULL | 3 | 千代田区大手町1丁目 | 2020-06-29T09:55:27 |
8003000051 | area | 東京都 | 13101004001 | 131010040 | NULL | 3 | 千代田区有楽町1丁目 | 2020-06-29T09:55:27 |
Redshitでデータを確認
統計データテーブル
select * from cm_kobayashi_test.estat_data;
statsdataid | cat01 | cat02 | area | unit | cnt | created_datetime |
---|---|---|---|---|---|---|
8003000051 | 0010 | 1 | 13101008002 | 人 | 933 | 2020-06-29T09:55:27 |
8003000051 | 0010 | 1 | 13101009004 | 人 | 605 | 2020-06-29T09:55:27 |
8003000051 | 0010 | 1 | 13101016002 | 人 | 820 | 2020-06-29T09:55:27 |
8003000051 | 0010 | 1 | 13101017002 | 人 | 266 | 2020-06-29T09:55:27 |
8003000051 | 0010 | 1 | 13101018002 | 人 | 3199 | 2020-06-29T09:55:27 |
この様な形でマスタテーブルと統計データテーブルにデータを分離して登録できました。
まとめ
XplentyのREST APIコンポーネントを使ってe-stat APIからデータを取得してみました。「データがJsonの深い階層に存在する」、「データをコピーして使い回す」、「データを結合する」といった複雑な処理もレスポンスの形をしっかり把握して手順を踏めばXplentyで処理できます。
最後まで読んで頂いてありがとうございました。